package org.southyin.securitysamples.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.southyin.securitysamples.model.Customer;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class POIUtils {
    /**
     * 将 查询的数据 存入 Excel
     *
     * @param list
     * @return
     */
    public static ResponseEntity<byte[]> exportExcel(List<Customer> list) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        // 设置背景色
        headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置边框
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        // 设置日期格式
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
        // 创建 sheet
        HSSFSheet sheet = workbook.createSheet("用户信息");
        // 创建第一行
        HSSFRow row0 = sheet.createRow(0);
        // 创建单元格
        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue("用户编号");
        cell0.setCellStyle(headerStyle);
        HSSFCell cell1 = row0.createCell(1);
        cell1.setCellValue("用户邮箱");
        cell1.setCellStyle(headerStyle);
        HSSFCell cell2 = row0.createCell(2);
        cell2.setCellValue("用户密码");
        cell2.setCellStyle(headerStyle);
        HSSFCell cell3 = row0.createCell(3);
        cell3.setCellValue("用户昵称");
        cell3.setCellStyle(headerStyle);
        HSSFCell cell4 = row0.createCell(4);
        cell4.setCellValue("用户性别");
        cell4.setCellStyle(headerStyle);
        HSSFCell cell5 = row0.createCell(5);
        cell5.setCellValue("用户坐标");
        cell5.setCellStyle(headerStyle);
        HSSFCell cell6 = row0.createCell(6);
        cell6.setCellValue("用户签名");
        cell6.setCellStyle(headerStyle);
        HSSFCell cell7 = row0.createCell(7);
        cell7.setCellValue("注册时间");
        cell7.setCellStyle(headerStyle);
        HSSFCell cell8 = row0.createCell(8);
        cell8.setCellValue("是否启用");
        cell8.setCellStyle(headerStyle);
        HSSFCell cell9 = row0.createCell(9);
        cell9.setCellValue("是否删除");
        cell9.setCellStyle(headerStyle);
        for (int i = 0; i < list.size(); i++) {
            Customer customer = list.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(customer.getId());
            row.createCell(1).setCellValue(customer.getEmail());
            row.createCell(2).setCellValue(customer.getPassword());
            row.createCell(3).setCellValue(customer.getNickname());
            row.createCell(4).setCellValue(customer.getGender() == true ? "女" : "男");
            row.createCell(5).setCellValue(customer.getLocation());
            row.createCell(6).setCellValue(customer.getSign());
            HSSFCell cell = row.createCell(7);
            cell.setCellStyle(dateStyle);
            cell.setCellValue(customer.getJoinTime());
            row.createCell(8).setCellValue(customer.getEnabled() == true ? "是" : "否");
            row.createCell(9).setCellValue(customer.getDelMark() == true ? "否" : "是");
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders httpHeaders = new HttpHeaders();
        try {
            httpHeaders.setContentDispositionFormData("attachment", new String("用户信息表.xls".getBytes("utf-8"), "ISO-8859-1"));
            httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), httpHeaders, HttpStatus.CREATED);
    }

    /**
     * 将 Excel 数据解析成 list
     *
     * @param file
     * @return
     */
    public static List<Customer> importExcel(MultipartFile file) {
        List<Customer> list = new ArrayList<>();
        try {
            // 根据 MultipartFile 创建 HSSFWorkbook
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            // 获取所有 sheet 数量
            int numberOfSheets = workbook.getNumberOfSheets();
            // 遍历所有 sheet
            for (int i = 0; i < numberOfSheets; i++) {
                HSSFSheet sheet = workbook.getSheetAt(i);
                // sheet 中 行的数量
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 1; j < physicalNumberOfRows; j++) {
                    Customer customer = new Customer();
                    // 获取行
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    }
                    // 获取有多少单元格
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        // 获取每个单元格
                        HSSFCell cell = row.getCell(k);
                        // 获取数据格式
                        switch (cell.getCellType()) {
                            case STRING:
                                switch (k) {
                                    case 1:
                                        customer.setEmail(cell.getStringCellValue());
                                        break;
                                    case 2:
                                        customer.setPassword(cell.getStringCellValue());
                                        break;
                                    case 3:
                                        customer.setNickname(cell.getStringCellValue());
                                        break;
                                    case 4:
                                        customer.setGender(cell.getStringCellValue().equals("男") ? false : true);
                                        break;
                                    case 5:
                                        customer.setLocation(cell.getStringCellValue());
                                        break;
                                    case 6:
                                        customer.setSign(cell.getStringCellValue());
                                        break;
                                    case 8:
                                        customer.setEnabled(cell.getStringCellValue().equals("是") ? true : false);
                                        break;
                                    case 9:
                                        customer.setDelMark(cell.getStringCellValue().equals("是") ? false : true);
                                        break;
                                }
                            default:
                                switch (k) {
                                    case 7:
                                        customer.setJoinTime(cell.getDateCellValue());
                                        break;
                                }
                        }
                    }
                    list.add(customer);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }
}
